﻿Imports MySql.Data.MySqlClient

Public Class add_Presonal5_1

    Public SQL As MySqlConnection
    Dim keydept As String
    Dim Maxdeptid As String

    Public Sub New(ByRef SQLConnection As MySqlConnection)
        InitializeComponent()
        SQL = SQLConnection
    End Sub

    Private Sub add_Presonal5_1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        loadwindow()
    End Sub

    Private Sub ButtonAddCompany_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonAddCompany.Click
        Dim addNewCompany As String
        addNewCompany = InputBox("Please Enter Your Company Name", "Add New Company", "")

        If addNewCompany.Length = 0 Then
            Exit Sub
        End If

        ComboBoxCompany.Items.Add(addNewCompany)
    End Sub

    Private Sub ButtonAddDevision_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonAddDevision.Click
        Dim addNewDevision As String
        addNewDevision = InputBox("Please Enter Your Devision Name", "Add New Devision", "")

        If addNewDevision.Length = 0 Then
            Exit Sub
        End If

        ComboBoxDevi.Items.Add(addNewDevision)
    End Sub

    Private Sub ButtonAddDepar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonAddDepar.Click
        Dim addNewDepart As String
        addNewDepart = InputBox("Please Enter Your Department Name", "Add New Depart", "")

        If addNewDepart.Length = 0 Then
            Exit Sub
        End If

        ComboBoxDepar.Items.Add(addNewDepart)
    End Sub

    Private Sub ComboBoxDevi_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBoxDevi.SelectedIndexChanged
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader


        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If

        mySqlCommand.CommandText = "SELECT DISTINCT dept_name FROM Department where dev_name = '" & ComboBoxDevi.SelectedItem.ToString() & "' ;"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader

        ComboBoxDepar.Items.Clear()

        While (mySqlReader.Read())

            ComboBoxDepar.Items.Add(mySqlReader("dept_name"))

        End While

        SQL.Close()

        keydept = ""

    End Sub

    Private Sub ComboBoxCompany_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBoxCompany.SelectedIndexChanged
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader


        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If

        mySqlCommand.CommandText = "SELECT DISTINCT dev_name FROM Department where comp_name = '" & ComboBoxCompany.SelectedItem.ToString() & "' ;"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader

        ComboBoxDepar.Items.Clear()
        ComboBoxDevi.Items.Clear()

        While (mySqlReader.Read())

            ComboBoxDevi.Items.Add(mySqlReader("dev_name"))

        End While

        SQL.Close()
    End Sub

    Private Sub checkDepartment()
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader


        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If

        mySqlCommand.CommandText = "SELECT dept_id FROM Department where (comp_name = '" & ComboBoxCompany.SelectedItem.ToString() & "') and (dev_name = '" & ComboBoxDevi.SelectedItem.ToString() & "') and dept_name = '" & ComboBoxDepar.SelectedItem.ToString() & "' ;"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader

        While (mySqlReader.Read())

            keydept = mySqlReader("dept_id")

        End While
        'MsgBox(keydept)
        SQL.Close()


        If keydept = "" Then
            InsertDepartment()

        Else
            InsertEmployee()

        End If

    End Sub
    Private Sub InsertDepartment()

        Dim MaxDept As String
        Dim MaxDept_toint As Integer
        Dim MySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader

        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If
        MySqlCommand.CommandText = "Select dept_id from testremote.department order by dept_id DESC limit 1;"
        MySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = MySqlCommand
        mySqlReader = MySqlCommand.ExecuteReader
        While (mySqlReader.Read())

            MaxDept = mySqlReader("dept_id")

        End While

        SQL.Close()
        MaxDept_toint = CInt(MaxDept) + 1

        MaxDept = CStr(MaxDept_toint)
        '---------------------------------------------------------------------------------------
        addZero(MaxDept)

        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If

        MySqlCommand.CommandText = "INSERT INTO Department (dept_id,dept_name,dev_name,comp_name) VALUES ('" & Maxdeptid & "','" & ComboBoxDepar.SelectedItem.ToString() & "', '" & ComboBoxDevi.SelectedItem.ToString() & "', '" & ComboBoxCompany.SelectedItem.ToString() & "');"
        MySqlCommand.CommandType = CommandType.Text
        MySqlCommand.Connection = SQL

        MySqlCommand.ExecuteNonQuery()


        SQL.Close()
        '------------------------------------------------------------------------------------------------------
        checkDepartment()

    End Sub
    Private Sub InsertEmployee()

        Dim MySqlCommand As New MySqlCommand
        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If
        Try
            MySqlCommand.CommandText = "INSERT INTO Employee (emp_code,dept_id,emp_name,emp_surname,emp_position,emp_level,emp_start_date,emp_gender,emp_thai_id,emp_status) VALUES ('" & MaskedEmCode.Text & "','" & keydept & "','" & TextName.Text & "','" & TextSurName.Text & "','" & TextPosition.Text & "','" & TextLevel.Text & "','" & DateTimePicker1.Value.Year.ToString() & "-" & DateTimePicker1.Value.Month.ToString() & "-" & DateTimePicker1.Value.Day.ToString() & "','" & ComboBoxGender.SelectedItem.ToString() & "','" & MaskedTextIDCard.Text & "','1');"
            MySqlCommand.CommandType = CommandType.Text
            MySqlCommand.Connection = SQL

            MySqlCommand.ExecuteNonQuery()

            SQL.Close()

            MaskedEmCode.Text = ""
            MaskedTextIDCard.Text = ""
            TextLevel.Text = ""
            TextName.Text = ""
            TextPosition.Text = ""
            TextSurName.Text = ""
            ComboBoxDepar.Items.Clear()
            ComboBoxDevi.Items.Clear()
            loadwindow()

        Catch
            MsgBox("Your Employee Code Repetitive ")
        End Try
    End Sub
    Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSave.Click

        Dim respone As Object

        If Len(TextName.Text) = 0 Or Len(TextSurName.Text) = 0 Or Len(TextLevel.Text) = 0 Or Len(TextPosition.Text) = 0 Or Len(MaskedEmCode.Text) = 0 Or Len(ComboBoxCompany.Text) = 0 Or Len(ComboBoxDepar.Text) = 0 Or Len(ComboBoxDevi.Text) = 0 Then
            MsgBox("Please Check field data", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Warning Message")
            Exit Sub
        End If

        respone = MsgBox("Are you sure to save data", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")

        If respone = 1 Then
            checkDepartment()

        End If

    End Sub

    Private Sub ComboBoxDepar_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBoxDepar.SelectedIndexChanged
        keydept = ""
    End Sub

    Public Sub addZero(ByVal sud As String)

        Dim length As Integer

        length = Len(sud)

        If (length <= 5) Then


            If (length = 4) Then
                sud = "0" + sud
                Maxdeptid = sud
            End If
            If (length = 3) Then
                sud = "00" + sud
                Maxdeptid = sud
            End If
            If (length = 2) Then
                sud = "000" + sud
                Maxdeptid = sud
            End If
            If (length = 1) Then
                sud = "0000" + sud
                Maxdeptid = sud
            End If
        Else
            Maxdeptid = sud
        End If


    End Sub

    Private Sub loadwindow()



        ComboBoxGender.Text = ComboBoxGender.Items(0)

        ComboBoxCompany.Items.Clear()

        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader

        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        mySqlCommand.CommandText = "SELECT Distinct comp_name FROM Department;"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader


        While (mySqlReader.Read())

            ComboBoxCompany.Items.Add(mySqlReader("comp_name"))

        End While

        SQL.Close()
    End Sub

    Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

    End Sub

    Private Sub GroupBox2_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox2.Enter

    End Sub

    Private Sub ButtonMenu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonMenu.Click
        Dim NextForm As main = New main()
        NextForm.Show()
        Me.Close()
    End Sub

    Private Sub ButtonClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonClear.Click
        MaskedEmCode.Text = ""
        MaskedTextIDCard.Text = ""
        TextLevel.Text = ""
        TextName.Text = ""
        TextSurName.Text = ""
        TextPosition.Text = ""
        ComboBoxDepar.Items.Clear()
        ComboBoxDevi.Items.Clear()
        loadwindow()
    End Sub
End Class